-- DROP old DB if exists and create a new one
DROP DATABASE IF EXISTS `trading_journal`;
CREATE DATABASE `trading_journal` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `trading_journal`;

-- ========== accounts ==========
CREATE TABLE `accounts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== symbols ==========
CREATE TABLE `symbols` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== strategies ==========
CREATE TABLE `strategies` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== journal ==========
-- serial_no is the single AUTO_INCREMENT PK (no second auto column)
CREATE TABLE `journal` (
  `serial_no` INT NOT NULL AUTO_INCREMENT,
  `account_id` INT DEFAULT NULL,
  `symbol_id` INT DEFAULT NULL,
  `strategy_id` INT DEFAULT NULL,
  `timeframe` VARCHAR(20) DEFAULT NULL,
  `trade_type` ENUM('Buy','Sell') DEFAULT NULL,
  `quantity` DECIMAL(12,2) DEFAULT NULL,
  `tp` DECIMAL(12,2) DEFAULT NULL,
  `sl` DECIMAL(12,2) DEFAULT NULL,
  `rr_ratio` DECIMAL(6,2) DEFAULT NULL,
  `profit_loss` DECIMAL(12,2) DEFAULT NULL,
  `percent_change` DECIMAL(6,2) DEFAULT NULL,
  `start_date` DATE DEFAULT NULL,
  `close_date` DATE DEFAULT NULL,
  `follow_setup` ENUM('Yes','No') DEFAULT 'No',
  `emotions` VARCHAR(255) DEFAULT NULL,
  `comments` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`serial_no`),
  KEY `idx_account` (`account_id`),
  KEY `idx_symbol` (`symbol_id`),
  KEY `idx_strategy` (`strategy_id`),
  CONSTRAINT `fk_journal_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_journal_symbol`  FOREIGN KEY (`symbol_id`)  REFERENCES `symbols`  (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_journal_strategy`FOREIGN KEY (`strategy_id`) REFERENCES `strategies`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
